IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnAdmQGetOrganizationAuthorizedResourceList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnAdmQGetOrganizationAuthorizedResourceList]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* VSS File Path: $Archive: /PHSRAG/Applications/WebApplications/Insight_2.2.0/Database/Functions/fnAdmQGetOrganizationAuthorizedResourceList.sql $
* Name:		spAdmQGetOrganizationAuthorizedResourceList
* Purpose:  
* Gets list of authorized resources of a specific type that a user has access 
* to by virtue of organization membership admin rights.
*
* PARAMETERS
* Name    			Description					
* -------------  	-------------------------------------------
* domainUserId		Id of the domain user to whom access has been granted
* resourceType		resource type id for resources to be returned
*
* RESULT SET
* Name    			Description					
* --------------	-------------------------------------------
* FwkResourcId		An Id of the resource the user has access to
*
* RETURN VALUE
* Value    			Description					
* --------------	-------------------------------------------
* n/a				n/a
*
* ERRORS USED
* -------------------------------------------------------------
* n/a
*
******************************************************************/
CREATE FUNCTION [dbo].[fnAdmQGetOrganizationAuthorizedResourceList]
(
	@domainUserId			INTEGER,
	@resourceType			INTEGER
)

RETURNS @_resourceTable TABLE
   (
    ResourceId		INTEGER,
    AccessLevel		INTEGER
   )
AS
BEGIN

	--Get list of resources of a certain type that user has access to by 
	--virtue of organization membership admin rights.
	
	--Declare constants
	DECLARE @ORG_RELATIONSHIP_TYPE_PRIMARY		INTEGER
	DECLARE @agreementResourceTypeId		INTEGER
	DECLARE @protocolResourceTypeId		INTEGER
	DECLARE @TRUE					INTEGER
	DECLARE @FALSE					INTEGER
	
	--Initialize constants
	SET @ORG_RELATIONSHIP_TYPE_PRIMARY	= 1
	SET @agreementResourceTypeId  = 1
	SET @protocolResourceTypeId = 2
	SET @TRUE				= 1
	SET @FALSE				= 0
	
	--Declare locals
	DECLARE @_currentOrg			INTEGER
	DECLARE @_accessLevel			INTEGER

	--Initialize locals
	SET @_currentOrg			= 0
	SET @_accessLevel			= 0
	
	--final list of orgs to get resources for
	DECLARE @_orgs TABLE(
		Id			INTEGER,
		AccessLevel	INTEGER)
	
	--work area for recursive orgs that temporarily holds a single
	--parent org and it's descendants
	DECLARE @_recursiveOrgs TABLE(
		Id		INTEGER, 
		Depth		INTEGER)

	--get the non-recursive org membership orgs
	INSERT @_orgs
	SELECT om.FwkDomainOrganizationId,
			arm.RightBitmask		
	FROM AdmOrganizationMembership om
	INNER JOIN	AdmRights_Map arm
		ON	om.AdmRightsMapId = arm.Id
		AND 	om.IsRecursive = @FALSE
	INNER JOIN	FwkDomainUser du
		ON	arm.FwkDomainUserId = du.Id
		AND	du.Id = @domainUserId

	--get recursive org membership orgs
	DECLARE cursorForRecursiveOrgs CURSOR LOCAL FAST_FORWARD FOR
	SELECT om.FwkDomainOrganizationId,
			arm.RightBitmask		
	FROM AdmOrganizationMembership om
	INNER JOIN	AdmRights_Map arm
		ON	om.AdmRightsMapId = arm.Id
		AND 	om.IsRecursive = @TRUE
	INNER JOIN	FwkDomainUser du
		ON	arm.FwkDomainUserId = du.Id
		AND	du.Id = @domainUserId

	-- open cursor and get first org
	OPEN cursorForRecursiveOrgs
	FETCH NEXT FROM cursorForRecursiveOrgs INTO @_currentOrg, @_accessLevel

	-- loop through recursive orgs
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--Find any descendant org's
		DECLARE @_level			INTEGER
		DECLARE @_count			INTEGER

		SET @_level 		= 0
		SET @_count		= 0
		
		--make sure we begin with no orgs

		DELETE @_recursiveOrgs
		-- First load up the base org
		INSERT INTO @_recursiveOrgs
			SELECT
			do.Id							AS 'Id',
			@_level							AS 'Depth'
			FROM FwkDomainOrganization do
			WHERE do.Id = @_currentOrg
		
		SET @_count = @@ROWCOUNT
	
		-- Find the decendents
		WHILE @_count > 0 		
			BEGIN
			-- Insert each child org into result set
			INSERT INTO @_recursiveOrgs
				SELECT
				do.Id							AS 'Id',
				@_level+1						AS 'Depth'
				FROM FwkDomainOrganization do 
				INNER JOIN @_recursiveOrgs ro
				ON do.ParentOrganizationId = ro.Id 
				AND do.ParentOrganizationId <> do.Id
				WHERE ro.Depth = @_level
			SET @_count = @@ROWCOUNT
			SET @_level = @_level+1
		END -- WHILE

		--add this batch of recursive orgs to the list of orgs
		INSERT @_orgs
		SELECT ro.Id,
				@_accessLevel
		FROM @_recursiveOrgs ro
	
	      	-- get next recursive org on list
	FETCH NEXT FROM cursorForRecursiveOrgs INTO @_currentOrg, @_accessLevel

	END

	-- close and deallocate cursor
	CLOSE cursorForRecursiveOrgs
	DEALLOCATE cursorForRecursiveOrgs

	DECLARE @_distinctOrgRights TABLE(
		Id			INTEGER,
		AccessLevel	INTEGER)

	INSERT INTO	@_distinctOrgRights
	SELECT o.Id,
			SUM (DISTINCT (art.BitValue))
	FROM	@_orgs o
	INNER JOIN AccessRightType art
	ON o.AccessLevel & art.BitValue = art.BitValue
	GROUP BY o.Id

	IF ( @resourceType = @agreementResourceTypeId)
	BEGIN
		-- determine the collection of bitvalues that indicate access to agreements		
		declare @agreementsAccessSum int
		select @agreementsAccessSum = sum(BitValue) from AccessRightType where 
		ModuleBitMask = (select BitValue from ModuleType where [Name] = 'Agreements')

		--Get list of agreement resources that the user has access to by virtue 
		--of organization memberships admin rights
		INSERT @_resourceTable
		SELECT fr.Id,
				dor.AccessLevel
		FROM FwkResource fr
		INNER JOIN AgrAgreement_Fct af
			ON	fr.Id = af.ResourceId
		INNER JOIN @_distinctOrgRights dor
			ON 	af.OrganizationId = dor.Id 
		where ((dor.AccessLevel & @agreementsAccessSum) !=0)
	END

	ELSE IF ( @resourceType = @protocolResourceTypeId)
	BEGIN
		-- determine the collection of bitvalues that indicate access to protocols
		declare @protocolsAccessSum int
		select @protocolsAccessSum = sum(BitValue) from AccessRightType where 
		ModuleBitMask in (select BitValue from ModuleType where [Name] in ( 'Humans', 'Animals'))


		--Get list of protocol resources that the user has access to by virtue 
		--of organization memberships admin rights
		INSERT @_resourceTable
		SELECT fr.Id,
				dor.AccessLevel
		FROM FwkResource fr
		INNER JOIN Protocol p
			ON	fr.Id = p.FwkResourceId
		INNER JOIN @_distinctOrgRights dor
			ON 	p.PIFwkDomainOrganizationId = dor.Id 
		WHERE p.IsCurrent = 1
		and ((dor.AccessLevel & @protocolsAccessSum) !=0)

	END
			
RETURN 

END
GO
